using System;
using System.Data;
using System.Data.SqlClient;
using DataAccessLayer;
using EntityObjectLayer;

namespace BusinessLogicLayer
{
    public class BLBehaviour
    {
        public DataSet SelectAll()
        {
            DataTable dt=Common.GetFromDataTable("pr_tblBehaviour_SelectAll");
            return dt.DataSet;
        }

        public int updateConduct(string p, int m, string e)
        {
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 10);
            PupilID.Value = p;

            SqlParameter MonthOfEval = new SqlParameter("@MonthOfEval", SqlDbType.Int, 4);
            MonthOfEval.Value = m;

            SqlParameter Eval = new SqlParameter("@Eval", SqlDbType.NVarChar, 10);
            Eval.Value = e;

            int returnInt = Common.Execute("pr_tblBehaviour_updateConduct", PupilID, MonthOfEval, Eval);

            return returnInt;
        }

        public int Delete(BEBehaviour beBehaviour)
        {
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 10);
            PupilID.Value = beBehaviour.sPupilID;

            SqlParameter MonthOfEval = new SqlParameter("@MonthOfEval", SqlDbType.Int, 4);
            MonthOfEval.Value = beBehaviour.iMonthOfEval;

            return Common.Execute("pr_tblBehaviour_Delete", PupilID, MonthOfEval);
        }

        public DataSet getClassList()
        {
            DataTable dt=Common.GetFromDataTable("pr_tblClass_SelectName");
            return dt.DataSet;
        }

        public DataSet getClassForConductEval(string c)
        {
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.Char, 10);
            Class.Value = c;
            DataTable classList = Common.GetFromDataTable("pr_tblPupil_loadClassList", Class);

            DataTable result = new DataTable();
            DataColumn dc;
            //Add column
            dc = new DataColumn();
            dc.ColumnName = "PupilID";
            dc.Caption = "PupilID";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = true;
            dc.Unique = true;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "FullName";
            dc.Caption = "FullName";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = true;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M9";
            dc.Caption = "M9";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M10";
            dc.Caption = "M10";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M11";
            dc.Caption = "M11";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M12";
            dc.Caption = "M12";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M1";
            dc.Caption = "M1";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M2";
            dc.Caption = "M2";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M3";
            dc.Caption = "M3";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M4";
            dc.Caption = "M4";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);

            dc = new DataColumn();
            dc.ColumnName = "M5";
            dc.Caption = "M5";
            dc.DataType = Type.GetType("System.String");
            dc.ReadOnly = false;
            dc.Unique = false;
            result.Columns.Add(dc);


            //Add row
            DataRow dr;
            for (int i = 0; i < classList.Rows.Count; i++)
            {
                SqlParameter p = new SqlParameter("@Pupil", SqlDbType.Char, 10);
                p.Value = classList.Rows[i]["PupilID"];
                DataTable pupil = Common.GetFromDataTable("pr_tblBehaviour_pupilConduct", p);
                dr = result.NewRow();
                dr["PupilID"] = classList.Rows[i]["PupilID"];
                dr["FullName"] = classList.Rows[i]["FullName"];
                for (int j = 0; j < pupil.Rows.Count; j++)
                {
                    dr["M" + pupil.Rows[j]["MonthOfEval"]] = pupil.Rows[j]["Eval"];
                }
                result.Rows.Add(dr);
            }
            DataSet ds = new DataSet();
            ds.Tables.Add(result);
            return ds;
        }

        public int getAbsent(string p)
        {
            SqlParameter Pupil = new SqlParameter("@Pupil", SqlDbType.Char, 10);
            Pupil.Value = p;
            SqlDataReader dr = Common.GetFromDataReader("pr_tblBehaviour_Absent", Pupil);
            dr.Read();
            return dr.GetInt32(0);
        }

        public DataSet getClass(string c)
        {
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.Char, 10);
            Class.Value = c;
            DataTable dt=Common.GetFromDataTable("pr_SelectForCheckAttendance", Class);
            return dt.DataSet;
        }

        public int createBehaviour(string pupilID,int i)

        {
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 10);
            PupilID.Value = pupilID;

            SqlParameter MonthOfEval = new SqlParameter("@MonthOfEval", SqlDbType.Int, 4);
            MonthOfEval.Value = i;
                
            Common.Execute("pr_tblBehaviour_Insert", PupilID,MonthOfEval);
            
            return 1;

        }

        public int resetTable()
        {
            return Common.Execute("pr_tblBehaviour_Reset");
        }

        public int markAbsent(string pupil)
        {
            SqlParameter Pupil = new SqlParameter("@Pupil", SqlDbType.Char, 10);
            Pupil.Value = pupil;
            return Common.Execute("pr_tblBehaviour_MarkAbsent", Pupil);
        }
    }
}